﻿using System;
using System.Data;
using System.Transactions;
using System.Data.Common;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Skyworth.CRM.Service.Entity;
using Skyworth.CRM.Service.IDAL;
using Skyworth.CRM.Common;

namespace Skyworth.CRM.DAL
{
    public class CustQuoteDAL : SuperDataBase, ICustQuoteDAL
    {
        public string Add(string strPrefix, CustQuote model)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select @NewCode=case isnull(MAX(FNumber),'1') when '1' then @Prefix + CONVERT(varchar(100),GETDATE(), 112) + '0001'");
                strSql.Append(" else @Prefix + CAST(cast(right(MAX(FNumber),12) as bigint)+1 as varchar) end");
                strSql.Append(" from T_Cust_Quote where LEFT(FNumber,len(@Prefix)+8) = @Prefix + CONVERT(varchar(100),GETDATE(), 112);");
                strSql.Append("insert into T_Cust_Quote(");
                strSql.Append("FDate,FNumber,FBusinessoppId,FMaker,FProductId,FQTY,FVolume,FQTYVolume,FUnit,FTax,FPrice,FTaxPrice,FAmount,FTaxAmount,FNote)");
                strSql.Append(" values (");
                strSql.Append("@FDate,@NewCode,@FBusinessoppId,@FMaker,@FProductId,@FQTY,@FVolume,@FQTYVolume,@FUnit,@FTax,@FPrice,@FTaxPrice,@FAmount,@FTaxAmount,@FNote)");
                //strSql.Append(";select @@IDENTITY");
                DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                DB.AddInParameter(cmd, "@Prefix", DbType.String, strPrefix);
                DB.AddInParameter(cmd, "@FDate", DbType.String, model.FDate);
                DB.AddOutParameter(cmd, "@NewCode", DbType.String, 50);
                DB.AddInParameter(cmd, "@FBusinessoppId", DbType.Int32, model.FBusinessoppId);
                DB.AddInParameter(cmd, "@FMaker", DbType.Int32, model.FMaker);
                DB.AddInParameter(cmd, "@FProductId", DbType.Int32, model.FProductId);
                DB.AddInParameter(cmd, "@FQTY", DbType.String, model.FQTY);
                DB.AddInParameter(cmd, "@FVolume", DbType.String, model.FVolume);
                DB.AddInParameter(cmd, "@FQTYVolume", DbType.String, model.FQTYVolume);
                DB.AddInParameter(cmd, "@FUnit", DbType.String, model.FUnit);
                DB.AddInParameter(cmd, "@FTax", DbType.String, model.FTax);
                DB.AddInParameter(cmd, "@FPrice", DbType.String, model.FPrice);
                DB.AddInParameter(cmd, "@FTaxPrice", DbType.String, model.FTaxPrice);
                DB.AddInParameter(cmd, "@FAmount", DbType.String, model.FAmount);
                DB.AddInParameter(cmd, "@FTaxAmount", DbType.String, model.FTaxAmount);
                DB.AddInParameter(cmd, "@FNote", DbType.String, model.FNote);
                DB.ExecuteNonQuery(cmd);
                string code = DB.GetParameterValue(cmd, "@NewCode").ToString();
                return code;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Update(CustQuote model)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("update T_Cust_Quote set ");
                strSql.Append("FDate=@FDate,");
                //strSql.Append("FNumber=@FNumber,");
                strSql.Append("FBusinessoppId=@FBusinessoppId,");
                strSql.Append("FMaker=@FMaker,");
                strSql.Append("FProductId=@FProductId,");
                strSql.Append("FQTY=@FQTY,");
                strSql.Append("FVolume=@FVolume,");
                strSql.Append("FQTYVolume=@FQTYVolume,");
                strSql.Append("FUnit=@FUnit,");
                strSql.Append("FTax=@FTax,");
                strSql.Append("FPrice=@FPrice,");
                strSql.Append("FTaxPrice=@FTaxPrice,");
                strSql.Append("FAmount=@FAmount,");
                strSql.Append("FTaxAmount=@FTaxAmount,");
                strSql.Append("FNote=@FNote");
                strSql.Append(" where FItemId=@FItemId");
                DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                DB.AddInParameter(cmd, "@FItemId", DbType.Int32, model.FItemId);
                DB.AddInParameter(cmd, "@FDate", DbType.String, model.FDate);
                //DB.AddInParameter(cmd, "@FNumber", DbType.String, model.FNumber);
                DB.AddInParameter(cmd, "@FBusinessoppId", DbType.Int32, model.FBusinessoppId);
                DB.AddInParameter(cmd, "@FMaker", DbType.Int32, model.FMaker);
                DB.AddInParameter(cmd, "@FProductId", DbType.Int32, model.FProductId);
                DB.AddInParameter(cmd, "@FQTY", DbType.String, model.FQTY);
                DB.AddInParameter(cmd, "@FVolume", DbType.String, model.FVolume);
                DB.AddInParameter(cmd, "@FQTYVolume", DbType.String, model.FQTYVolume);
                DB.AddInParameter(cmd, "@FUnit", DbType.String, model.FUnit);
                DB.AddInParameter(cmd, "@FTax", DbType.String, model.FTax);
                DB.AddInParameter(cmd, "@FPrice", DbType.String, model.FPrice);
                DB.AddInParameter(cmd, "@FTaxPrice", DbType.String, model.FTaxPrice);
                DB.AddInParameter(cmd, "@FAmount", DbType.String, model.FAmount);
                DB.AddInParameter(cmd, "@FTaxAmount", DbType.String, model.FTaxAmount);
                DB.AddInParameter(cmd, "@FNote", DbType.String, model.FNote);
                DB.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Delete(int id)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("delete from T_Cust_Quote ");
                strSql.Append(" where FItemId=@FItemId");
                DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                DB.AddInParameter(cmd, "@FItemId", DbType.Int32, id);
                DB.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public CustQuote GetModel(int id)
        {
            try
            {
                CustQuote model = null;
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select  top 1 FItemId,FDate,FNumber,FBusinessoppId,FMaker,FProductId,FQTY,FVolume,FQTYVolume,FUnit,FTax,FPrice,FTaxPrice,FAmount,FTaxAmount,FNote from T_Cust_Quote ");
                strSql.Append(" where FItemId=@FItemId");
                DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                DB.AddInParameter(cmd, "@FItemId", DbType.Int32, id);
                using (IDataReader dr = DB.ExecuteReader(cmd))
                {
                    while (dr.Read())
                    {
                        model = new CustQuote();
                        if (dr["FItemId"].ToString() != "")
                        {
                            model.FItemId = int.Parse(dr["FItemId"].ToString());
                        }
                        if (dr["FDate"].ToString() != "")
                        {
                            model.FDate = DateTime.Parse(dr["FDate"].ToString());
                        }
                        model.FNumber = dr["FNumber"].ToString();
                        if (dr["FBusinessoppId"].ToString() != "")
                        {
                            model.FBusinessoppId = int.Parse(dr["FBusinessoppId"].ToString());
                        }
                        if (dr["FMaker"].ToString() != "")
                        {
                            model.FMaker = int.Parse(dr["FMaker"].ToString());
                        }
                        if (dr["FProductId"].ToString() != "")
                        {
                            model.FProductId = int.Parse(dr["FProductId"].ToString());
                        }
                        if (dr["FQTY"].ToString() != "")
                        {
                            model.FQTY = int.Parse(dr["FQTY"].ToString());
                        }
                        if (dr["FVolume"].ToString() != "")
                        {
                            model.FVolume = decimal.Parse(dr["FVolume"].ToString());
                        }
                        if (dr["FQTYVolume"].ToString() != "")
                        {
                            model.FQTYVolume = decimal.Parse(dr["FQTYVolume"].ToString());
                        }
                        model.FUnit = dr["FUnit"].ToString();
                        if (dr["FTax"].ToString() != "")
                        {
                            model.FTax = decimal.Parse(dr["FTax"].ToString());
                        }
                        if (dr["FPrice"].ToString() != "")
                        {
                            model.FPrice = decimal.Parse(dr["FPrice"].ToString());
                        }
                        if (dr["FTaxPrice"].ToString() != "")
                        {
                            model.FTaxPrice = decimal.Parse(dr["FTaxPrice"].ToString());
                        }
                        if (dr["FAmount"].ToString() != "")
                        {
                            model.FAmount = decimal.Parse(dr["FAmount"].ToString());
                        }
                        if (dr["FTaxAmount"].ToString() != "")
                        {
                            model.FTaxAmount = decimal.Parse(dr["FTaxAmount"].ToString());
                        }
                        model.FNote = dr["FNote"].ToString();
                    }
                }
                return model;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable Query(string strWhere)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append(@"select tcq.FItemId,tcq.FDate,tcq.FNumber,tcq.FBusinessoppId,tcb.FNumber as BssCode,tcq.FProductId,tcq.FQTY,
tcq.FVolume,tcq.FQTYVolume,tcq.FUnit,tcq.FTax,tcq.FPrice,tcq.FTaxPrice,tcq.FAmount,tcq.FTaxAmount,
tcq.FNote,tbp.ProductName,tcq.FMaker,tbe.EmpName FROM T_Cust_Quote as tcq
inner join T_Cust_Bussinessopp as tcb on tcq.FBusinessoppId=tcb.FItemId
inner join T_BasicData_Product as tbp on tcq.FProductId=tbp.ProductId
left join T_BasicData_Employee as tbe on tcq.FMaker=tbe.EmpId
where 1=1");
                if (strWhere.Trim() != "")
                {
                    strSql.Append(strWhere);
                }
                DataSet ds = DB.ExecuteDataSet(CommandType.Text, strSql.ToString());
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 分页获得数据列表
        /// </summary>
        public DataTable Query(string strWhere, string orderKey, int pageSize, int pageIndex, out int recordCount)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append(@"select tcq.FItemId,tcq.FDate,tcq.FNumber,tcq.FBusinessoppId,tcb.FNumber as BssCode,tcq.FProductId,tcq.FQTY,
tcq.FVolume,tcq.FQTYVolume,tcq.FUnit,tcq.FTax,tcq.FPrice,tcq.FTaxPrice,tcq.FAmount,tcq.FTaxAmount,
tcq.FNote,tbp.ProductName,tcq.FMaker,tbe.EmpName FROM T_Cust_Quote as tcq
inner join T_Cust_Bussinessopp as tcb on tcq.FBusinessoppId=tcb.FItemId
inner join T_BasicData_Product as tbp on tcq.FProductId=tbp.ProductId
left join T_BasicData_Employee as tbe on tcq.FMaker=tbe.EmpId
where 1=1");
                if (strWhere.Trim() != "")
                {
                    strSql.Append(strWhere);
                }
                string countsql = "select count(1) from (" + strSql.ToString() + ") as FCount";
                //计算开始索引和结束索引
                int startIndex = pageSize * (pageIndex - 1) + 1;
                int endIndex = startIndex + pageSize - 1;
                //调用分页Sql方法
                string pageSql = Pagination.PageSql(strSql.ToString(), orderKey, startIndex, endIndex);
                //记录总数
                DbCommand cmd = DB.GetSqlStringCommand(countsql);
                recordCount = Convert.ToInt32(DB.ExecuteScalar(cmd));
                cmd = DB.GetSqlStringCommand(pageSql);
                return DB.ExecuteDataSet(cmd).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}